import pandas as pd
from DatetimeUtils import choose_day
import time
"""
交易日
"""


def insert_history_data_trade_cal(engine, pro):
    """
    保存每年的交易日数据
    """
    cal_first = 1992
    current_year = choose_day(0).strftime('%Y')
    for i in range(cal_first, int(current_year)+1):
        query_start = '%d' % i + '0101'
        query_end = '%d' % i + '1231' # 查询结束
        sql = 'select t.* from trade_cal t WHERE t.cal_date >= ' + query_start + ' and t.cal_date <=' + query_end
        df = pd.read_sql(sql, engine)

        if len(df) > 0:
            print('数据已存在')
        else:
            df = pro.trade_cal(exchange='', start_date=query_start, end_date=query_end)
            df.to_sql('trade_cal', con=engine, index=False, index_label='cal_date', if_exists='append')
            print('(%s)' %df)

        time.sleep(1)

def insert_current_trade_cal(engine, pro):
    """
    保存当年交易日信息
    """
    current_year = choose_day(0).strftime('%Y')
    query_start = current_year + '0101'
    query_end = current_year + '1231'  # 查询结束
    sql = 'select t.* from trade_cal t WHERE t.cal_date >= ' + query_start + ' and t.cal_date <=' + query_end
    df = pd.read_sql(sql, engine)

    if len(df) > 0:
        print('数据已存在')
    else:
        df = pro.trade_cal(exchange='', start_date=query_start, end_date=query_end)
        df.to_sql('trade_cal', con=engine, index=False, index_label='cal_date', if_exists='append')
        print('(%s)' % df)


def get_useful_cal_day(engine_):
    """
    获取指定时间段内的有效交易日
    """
    current_day = choose_day(1).strftime('%Y%m%d')
    that_day = choose_day(10).strftime('%Y%m%d')
    sql = 'select c.* from trade_cal c WHERE c.cal_date >= ' + that_day + ' and c.cal_date <= ' + current_day + ' and c.is_open = "1"  order by c.cal_date desc limit 1'
    real_cal_days = pd.read_sql(sql, engine_)
    if len(real_cal_days) == 0:
        print("指定期限内无有效交易日")
        return

    return real_cal_days.loc[0].cal_date
